package gogoqiu.java.base;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import com.alibaba.druid.pool.DruidDataSource;

import java.net.InetAddress;
import java.net.UnknownHostException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class mysql {
    private static String getMysqlUrl( String ip, int port, String database ){
        String url = String.format( "jdbc:mysql://%s:%d/%s?useSSL=false&serverTimezone=UTC",
            ip, port, database );
        return url;
    }
    /*
    // Java 文本块 + 占位符（等效效果）
String name = "Java";
int version = 17;
String str = String.format("""
    语言：%s，
    版本：%d""", name, version);
// 需启用预览功能（--enable-preview）
String name = "Java";
int version = 21;
String str = STR."""
    语言：\{name}，
    版本：\{version}"""; // 用 \{} 嵌入变量
     */
    public static Connection getConnection( int port, String username, String password, 
            String database ) throws SQLException, UnknownHostException{        
        InetAddress localHost = InetAddress.getLocalHost();
        // 获取主机名
        String ip = webDef.WanHostIP.aliyun.getValue();
        if( localHost.getHostName().equals(webDef.Hostname.aliyun.getValue()) ){
            ip = webDef.LanHostIP.localhost.getValue();
        };
        //String url = "jdbc:mysql://\{}:\{}/\{}?useSSL=false&serverTimezone=UTC";
        //String url = String.format( "jdbc:mysql://%s:%d/%s?useSSL=false&serverTimezone=UTC",
        //      ip, port, database );
        return DriverManager.getConnection( getMysqlUrl( ip, port, database ), username, password);
    }

    // 
    public static DataSource getDruidPool( String host, int port, String username, String password, 
            String database, int connectionLimit ) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(getMysqlUrl(host, port, database));
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setMaxActive(connectionLimit); // 最大连接数
        dataSource.setMinIdle(2); // 最小空闲连接数
        dataSource.setMaxWait(30000); // 获取连接的最大等待时间
        return dataSource;
    }

    public static DataSource getDruidPool( String host, int port, String username, String password, 
            String database ) {
        return getDruidPool(host, port, username, password, database, 20);
    }

    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/your_database_name?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "your_password";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            DataSource pool = getDruidPool(url, 0, username, password, password);
            conn = pool.getConnection();
            conn.prepareStatement("");
             // 2. 编写SQL（以查询为例）
            String sql = "SELECT id, name, age FROM users WHERE age > ?";

            // 3. 创建PreparedStatement（防止SQL注入）
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 18); // 设置参数（第一个?的值为18）

            // 4. 执行查询，获取结果集
            rs = pstmt.executeQuery();

            // 1. 加载驱动（MySQL 8.x 可省略，JDBC 4.0 会自动加载）
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 建立连接
            conn = DriverManager.getConnection(url, username, password);

            // 3. 执行 SQL 查询
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM your_table");

            // 4. 处理结果
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("id: " + id + ", name: " + name);
                Timestamp createTime = rs.getTimestamp("create_time");
                System.out.println("Timestamp: " + createTime); // 输出格式如：2023-10-01 12:34:56.789
                createTime.getTime();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5. 关闭资源（逆序关闭）
            try { if (rs != null) rs.close(); } catch (Exception e) { e.printStackTrace(); }
            try { if (stmt != null) stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}